-- FlowPlan项目统计相关存储过程
-- 说明: 提供项目统计和分析相关的存储过程

-- 获取项目统计信息存储过程
CREATE OR REPLACE PROCEDURE get_project_stats(
    IN p_project_id BIGINT,
    OUT p_total_todos BIGINT,
    OUT p_completed_todos BIGINT,
    OUT p_in_progress_todos BIGINT,
    OUT p_pending_todos BIGINT,
    OUT p_blocked_todos BIGINT,
    OUT p_completion_percentage NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    SELECT 
        COUNT(id),
        COUNT(CASE WHEN status = 'DONE' THEN 1 END),
        COUNT(CASE WHEN status = 'IN_PROGRESS' THEN 1 END),
        COUNT(CASE WHEN status = 'TODO' THEN 1 END),
        COUNT(CASE WHEN status = 'BLOCKED' THEN 1 END),
        ROUND(
            CASE 
                WHEN COUNT(id) > 0 
                THEN (COUNT(CASE WHEN status = 'DONE' THEN 1 END) * 100.0 / COUNT(id))
                ELSE 0 
            END, 2
        )
    INTO 
        p_total_todos, p_completed_todos, p_in_progress_todos, 
        p_pending_todos, p_blocked_todos, p_completion_percentage
    FROM todo 
    WHERE project_id = p_project_id;
END;
$$;

-- 生成项目报告存储过程
CREATE OR REPLACE PROCEDURE generate_project_report(
    IN p_project_id BIGINT,
    OUT p_report JSONB
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_project_name VARCHAR(200);
    v_project_status VARCHAR(20);
    v_total_todos BIGINT;
    v_completed_todos BIGINT;
    v_completion_percentage NUMERIC;
    v_team_members JSONB;
BEGIN
    -- 获取项目基本信息
    SELECT project_name, status INTO v_project_name, v_project_status
    FROM project WHERE id = p_project_id;
    
    -- 获取任务统计
    CALL get_project_stats(
        p_project_id, 
        v_total_todos, 
        v_completed_todos, 
        NULL, 
        NULL, 
        NULL, 
        v_completion_percentage
    );
    
    -- 获取团队成员信息
    SELECT jsonb_agg(jsonb_build_object(
        'user_id', u.id,
        'username', u.username,
        'role', u.role,
        'assigned_todos', COUNT(t.id),
        'completed_todos', COUNT(CASE WHEN t.status = 'DONE' THEN 1 END)
    ))
    INTO v_team_members
    FROM sys_user u
    LEFT JOIN todo t ON u.id = t.assignee_id AND t.project_id = p_project_id
    WHERE u.id IN (
        SELECT DISTINCT assignee_id FROM todo WHERE project_id = p_project_id AND assignee_id IS NOT NULL
        UNION
        SELECT creator_id FROM project WHERE id = p_project_id
    )
    GROUP BY u.id, u.username, u.role;
    
    -- 构建报告JSON
    p_report := jsonb_build_object(
        'project_id', p_project_id,
        'project_name', v_project_name,
        'project_status', v_project_status,
        'total_todos', v_total_todos,
        'completed_todos', v_completed_todos,
        'completion_percentage', v_completion_percentage,
        'team_members', COALESCE(v_team_members, '[]'::jsonb),
        'generated_at', CURRENT_TIMESTAMP
    );
END;
$$;

-- 批量更新项目状态存储过程
CREATE OR REPLACE PROCEDURE batch_update_project_status(
    IN p_project_ids BIGINT[],
    IN p_new_status VARCHAR(20),
    IN p_operator_id BIGINT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_project_id BIGINT;
    v_updated_count INTEGER := 0;
BEGIN
    -- 验证状态值
    IF p_new_status NOT IN ('PLANNING', 'IN_PROGRESS', 'TESTING', 'COMPLETED', 'CANCELLED') THEN
        RAISE EXCEPTION '无效的项目状态: %', p_new_status;
    END IF;
    
    -- 遍历项目ID数组
    FOREACH v_project_id IN ARRAY p_project_ids
    LOOP
        -- 更新项目状态
        UPDATE project 
        SET status = p_new_status, update_time = CURRENT_TIMESTAMP
        WHERE id = v_project_id;
        
        -- 记录审计日志
        INSERT INTO audit_log (operator_id, operation_type, resource_type, resource_id, 
                              old_value, new_value)
        VALUES (p_operator_id, 'UPDATE', 'PROJECT', v_project_id,
                (SELECT jsonb_build_object('status', status) FROM project WHERE id = v_project_id),
                jsonb_build_object('status', p_new_status));
        
        v_updated_count := v_updated_count + 1;
    END LOOP;
    
    RAISE NOTICE '成功更新 % 个项目的状态为 %', v_updated_count, p_new_status;
END;
$$;